
print 'Personalplaner Users'
go

if not exists (select 1 from sys.syslogins where loginname = 'prsadmin')
begin 
   -- create the login
   exec master.dbo.sp_addlogin @loginame = N'prsadmin', @passwd = 'Personalplaner01'--, @defdb = N'master', @deflanguage = N'Deutsch'
   
   declare @policy_checked int
   declare @is_expiration_checked int
   
   --SQL2005 and newer
   if (SELECT CAST(LEFT(CAST(SERVERPROPERTY('productversion') as varchar), 3) AS decimal)) >= 9
   begin
      -- remove the policy for the password
      select @policy_checked = is_policy_checked from sys.sql_logins where name = 'prsadmin'
	  select @is_expiration_checked = is_expiration_checked from sys.sql_logins where name = 'prsadmin'
	  exec('ALTER LOGIN ' + 'prsadmin' + ' WITH CHECK_POLICY  = off')
	  -- alter the password
	  exec('ALTER LOGIN ' + 'prsadmin' + ' WITH PASSWORD  = ''' + 'personalplaner' + '''')
   end
   else
   begin
      -- alter the password
      exec ('sp_password null, ''' + 'personalplaner' + ''' , ' + 'prsadmin')
   end   
   
   -- reset the policy
   if @policy_checked=1
   begin
      exec('ALTER LOGIN ' + 'prsadmin' + ' WITH CHECK_POLICY  = on')
   end
   if @is_expiration_checked=1
   begin
      exec('ALTER LOGIN ' + 'prsadmin' + ' WITH CHECK_EXPIRATION = on')
   end
	
end
go
print 'Login: prsadmin created'
go
   
   
if not exists (select 1 from sys.syslogins where loginname = 'prsuser')
begin 
   -- create the login
   exec master.dbo.sp_addlogin @loginame = N'prsuser', @passwd = 'Personalplaner01'--, @defdb = N'master', @deflanguage = N'Deutsch'
   
   declare @policy_checked int
   declare @is_expiration_checked int
   
   --SQL2005 and newer
   if (SELECT CAST(LEFT(CAST(SERVERPROPERTY('productversion') as varchar), 3) AS decimal)) >= 9
   begin
      -- remove the policy for the password
      select @policy_checked = is_policy_checked from sys.sql_logins where name = 'prsuser'
	  select @is_expiration_checked = is_expiration_checked from sys.sql_logins where name = 'prsuser'
	  exec('ALTER LOGIN ' + 'prsuser' + ' WITH CHECK_POLICY  = off')
	  -- alter the password
	  exec('ALTER LOGIN ' + 'prsuser' + ' WITH PASSWORD  = ''' + 'personalplaner' + '''')
   end
   else
   begin
      -- alter the password
      exec ('sp_password null, ''' + 'personalplaner' + ''' , ' + 'prsuser')
   end   
   
   -- reset the policy
   if @policy_checked=1
   begin
      exec('ALTER LOGIN ' + 'prsuser' + ' WITH CHECK_POLICY  = on')
   end
   if @is_expiration_checked=1
   begin
      exec('ALTER LOGIN ' + 'prsuser' + ' WITH CHECK_EXPIRATION = on')
   end
	
end
go
print 'Login: prsuser created'
go

----------------------------------------------------------------------------------------------
-- Create the Roles
----------------------------------------------------------------------------------------------

if not exists (select 1 from sysusers where status = 0 and name = 'prsadmins')
begin
   exec sp_addrole @rolename = N'prsadmins', @ownername = N'dbo'
   print 'Role: prsadmins created'
end

if not exists (select 1 from sysusers where status = 0 and name = 'prsusers')
begin
   exec sp_addrole @rolename = N'prsusers', @ownername = N'dbo'
   print 'Role: prsusers created'
end


----------------------------------------------------------------------------------------------
-- User und Rolle der DB zuweisen
----------------------------------------------------------------------------------------------

if not exists (select 1 from sysusers where name like 'prsadmin')
begin
   --exec sp_addrole @rolename = N'prsadmins', @ownername = N'dbo'
   exec dbo.sp_grantdbaccess @loginame = N'prsadmin', @name_in_db = N'prsadmin'
   exec dbo.sp_addrolemember @rolename = N'prsadmins', @membername = N'prsadmin'
   print 'User: prsadmin created'
end
go

if not exists (select 1 from sysusers where name like 'prsuser')
begin
   --exec sp_addrole @rolename = N'prsusers', @ownername = N'dbo'
   exec dbo.sp_grantdbaccess @loginame = N'prsuser', @name_in_db = N'prsuser'
   exec dbo.sp_addrolemember @rolename = N'prsusers', @membername = N'prsuser'
   print 'User: prsuser created'
end
go

